package db.tables;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.hsqldb.Types;

import db.DBObject;

public class DBMessageTable extends DBObject {
		
	public static final String TABLE_NAME="message";
	
	public static final String MESSAGE_ID_COL = TABLE_NAME+"_id",
			SENDER_ID_COL = "sender_id", RECEIVER_ID_COL = "receiver_id",
			PRIVATE_MSG_COL = "private",
			DATE_COL=TABLE_NAME+"_date",
			MESSAGE_TITLE_COL = TABLE_NAME+"_title", 
			MESSAGE_TEXT_COL=TABLE_NAME+"_text";

	public DBMessageTable() throws ClassNotFoundException, SQLException {
		super(TABLE_NAME, new Object[][] {
				{ MESSAGE_ID_COL, Types.BIGINT, SQL_IDENTITY_PRIMARY_KEY, "" },
				{ SENDER_ID_COL, Types.BIGINT, "", "" },
				{ RECEIVER_ID_COL, Types.BIGINT, "",""},
				{ PRIVATE_MSG_COL, Types.BOOLEAN,"",true},
				{ DATE_COL, Types.DATE,"",SQL_CURRENT_DATE},
				{ MESSAGE_TITLE_COL, Types.VARCHAR, "", "" },
				{ MESSAGE_TEXT_COL, Types.VARCHAR, 
					getSQLForeignKeyReference(DBUserTable.TABLE_NAME, SENDER_ID_COL, DBUserTable.USER_ID_COL)
				+getSQLForeignKeyReference(DBUserTable.TABLE_NAME, RECEIVER_ID_COL, DBUserTable.USER_ID_COL), "" }});
	}
	
	public boolean insertMessage(Object[] values){
		String[] columns={SENDER_ID_COL,RECEIVER_ID_COL,MESSAGE_TITLE_COL,MESSAGE_TEXT_COL,
				PRIVATE_MSG_COL};
		insert(columns, values);
		return true;
	}
	
	public ArrayList<ArrayList<Object>> getArrayListByUserID(Long user_id, Boolean private_msg){
		ArrayList<ArrayList<Object>> result=null;
		ResultSet rs=null;
		try {
			rs=getSimpleSelectResults(new String[]{MESSAGE_ID_COL,SENDER_ID_COL,DATE_COL,MESSAGE_TITLE_COL,MESSAGE_TEXT_COL},
					new String[]{RECEIVER_ID_COL,PRIVATE_MSG_COL}, new Object[]{user_id,private_msg}, 
					new String[]{tableName}, null, null, DATE_COL, SQL_ORDER_DESCENDING);
			result=getResultSetAsArrayList(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return result;
	}
}
